package com.vs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.vs.model.DocSearchForm;

public class DocumentOperation {

	private String insertString = "insert into documents(project_id,doc_type,doc_name,doc_path,added_by) values(?,?,?,?,?)";

	private String deleteString = "delete from documents where doc_id=?";

	private String searchString = "call get_document_list(?)";
	
	private String getInfoString = "call get_document_info(?)";
	
	public boolean deleteDoc(Integer docId) {
		System.out.println("deleteExpense called for expenseId=" + docId);
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(deleteString);
			ps.setInt(1, docId);
			ps.execute();
			ps.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return success;

	}

	public boolean addDoc(DocSearchForm docForm) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement psExpenseInsert = conn
					.prepareStatement(insertString);
			psExpenseInsert.setInt(1, docForm.getProjectId());
			psExpenseInsert.setString(2, docForm.getDocType());
			psExpenseInsert.setString(3, docForm.getFileName());
			psExpenseInsert.setString(4, docForm.getFilePath());
			psExpenseInsert.setString(5, docForm.getAddedBy());
			psExpenseInsert.execute();
			psExpenseInsert.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return success;
	}
	
	public DocSearchForm getDocInfo(Integer docId) {
		Connection conn = null;
		DocSearchForm doc = null;
		System.out.println("inside getDocInfo docId="+docId);
		
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(getInfoString);
			ps.setInt(1, docId);
			ResultSet rs = ps.executeQuery();
			
			if (rs.next()) {
				doc = new DocSearchForm();
				doc.setDocId(rs.getInt("doc_id"));
				doc.setProjectId(rs.getInt("project_id"));
				doc.setDocType(rs.getString("doc_type"));
				doc.setFileName(rs.getString("doc_name"));
				doc.setFilePath(rs.getString("doc_path"));
				doc.setAddedBy(rs.getString("added_by"));
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return doc;
	}

	public List<DocSearchForm> getDocList(DocSearchForm docForm) {
		Connection conn = null;
		List<DocSearchForm> docList = new ArrayList<DocSearchForm>();
		System.out.println("inside getDocList");
		System.out.println("doc ProjectId="
				+ docForm.getProjectId());
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(searchString);
			ps.setInt(1, docForm.getProjectId());
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {
				DocSearchForm doc = new DocSearchForm();
				doc.setDocId(rs.getInt("doc_id"));
				doc.setProjectId(rs.getInt("project_id"));
				doc.setDocType(rs.getString("doc_type"));
				doc.setFileName(rs.getString("doc_name"));
				doc.setFilePath(rs.getString("doc_path"));
				doc.setAddedBy(rs.getString("added_by"));
				docList.add(doc);
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("docList size:" + docList.size());
		return docList;
	}

	public static void main(String args[]) {
		// List<ExpenseForm> expenseList = new ExpensesOperation()
		// .getExpensesList();
		// System.out.println("size:" + expenseList.size());

	}

}
